///
// Created by 张越洋666 on 2025/11/7.
//
#include"database.h"
#include "admin_zsgc.h"
void initialize_system() {
    open_db();
    init_db();
    create_admin_user();
    create_employee();
    create_employee_record();
}

//插入管理员表数据
int insert_admin_user(ADMIN * admin){
    char sql[256];
    sprintf(sql,"INSERT INTO admin(name,phone,password)VALUES('%s','%s','%s');"
    ,admin->name,admin->phone,admin->password);
    char *errmsg = NULL;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr,"admin表注册失败:%s",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("admin表注册成功");
    return 0;
}
int update_admin_user(ADMIN * admin){
    char sql[256];
    sprintf(sql,"update admin set name='%s',phone='%s',password='%s' where id=%d;"
            ,admin->name,admin->phone,admin->password,admin->id);
    char *errmsg = NULL;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr,"admin表更新数据失败:%s",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("admin表更新数据成功");
    return 0;
}
int delete_admin_user(int id){
    char sql[256];
    sprintf(sql,"delete from admin  where id =%d;"
            ,id);
    char *errmsg = NULL;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr,"admin表删除数据失败:%s",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("admin表删除数据成功");
    return 0;
}


int query_admin_name(char* admin_name){
    // 定义查询SQL，根据姓名查询管理员信息
    const char *sql = NULL;

    if (admin_name == NULL){
        // 查询所有管理员记录，按姓名排序
        sql = "SELECT id, name, phone, password FROM admin ORDER BY name;";
    } else{
        // 根据姓名查询特定管理员记录
        sql = "SELECT id, name, phone, password FROM admin WHERE name = ?;";
    }

    // 定义SQL语句执行句柄
    sqlite3_stmt *stmt;

    // 预编译SQL语句
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if(rc != SQLITE_OK){
        fprintf(stderr, "预编译出错: %s\n", sqlite3_errmsg(db));
        return -1;
    }

    // 绑定参数（姓名）[1](@ref)
    if(admin_name != NULL)
        sqlite3_bind_text(stmt, 1, admin_name, -1, SQLITE_TRANSIENT);

    int found = 0; // 用于记录找到的管理员记录数量

    // 显示查询结果表头
    printf("\n=== 管理员查询结果 ===\n");
    printf("ID\t姓名\t电话\t\t密码\n");
    printf("----------------------------------------\n");

    // 执行查询并遍历结果
    while(sqlite3_step(stmt) == SQLITE_ROW){
        // 读取当前行的各列数据
        int id = sqlite3_column_int(stmt, 0);                    // 第0列：id
        const unsigned char *name = sqlite3_column_text(stmt, 1);  // 第1列：name
        const unsigned char *phone = sqlite3_column_text(stmt, 2); // 第2列：phone
        const unsigned char *password = sqlite3_column_text(stmt, 3); // 第3列：password

        // 格式化输出管理员记录
        printf("%d\t%s\t%s\t%s\n",
               id,
               name ? name : "NULL",
               phone ? phone : "NULL",
               password ? password : "NULL");
        found++; // 正确计数找到的记录数量
    }

    // 输出查询结果统计
    if(!found){
        if(admin_name != NULL){
            printf("未找到姓名为 '%s' 的管理员\n", admin_name);
        } else {
            printf("管理员表中暂无数据\n");
        }
    } else {
        printf("----------------------------------------\n");
        printf("共找到 %d 条管理员记录\n", found);
    }

    // 释放语句句柄
    sqlite3_finalize(stmt);
    return found; // 返回找到的记录数量
}
//插入员工表数据
int insert_employee_user(EMPLOYEE * employee){
    printf("请输入要添加的员工姓名、员工职位、员工工号");
    scanf("%19s%19s%19s", employee->employee_name,employee->position,employee->employee_num);
    char sql[256];
    sprintf(sql,"INSERT INTO employee(employee_name, position, "
                "employee_num)VALUES('%s','%s','%s');",employee->employee_name,
                employee->position,employee->employee_num);
    char *errmsg = NULL;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr,"employee表插入数据失败:%s",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("employee表插入数据成功");
    return 0;
}
int update_employee_user(EMPLOYEE * employee){
    char sql[256];
    sprintf(sql," update employee set employee_name='%s',position='%s',"
                " employee_num='%s', where employee_name=%d;"
            ,employee->employee_name,employee->position,employee->employee_num);
    char *errmsg = NULL;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr,"employee表更新数据失败:%s",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("employee表更新数据成功");
    return 0;
}
int delete_employee_user(EMPLOYEE * employee){
    printf("请输入要删除的员工姓名: ");
    scanf("%19s", employee->employee_name);
    char sql[256];
    sprintf(sql,"delete from employee where employee_name ='%s';"
            ,employee->employee_name);
    char *errmsg = NULL;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr,"employee表删除数据失败:%s",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("employee表删除数据成功");
    return 0;
}
int query_employee_name(char* employee_name){
    // 定义查询SQL，根据姓名查询员工信息
    const char *sql = NULL;

    if (employee_name == NULL){
        // 查询所有员工记录，按姓名排序
        sql = "SELECT employee_id, employee_name, position, employee_num  FROM employee ORDER BY employee_name;";
    } else{
        // 根据姓名查询特定员工记录
        sql = "SELECT employee_id, employee_name, position, employee_num  FROM employee WHERE employee_name = ?;";
    }

    // 定义SQL语句执行句柄
    sqlite3_stmt *stmt;

    // 预编译SQL语句
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if(rc != SQLITE_OK){
        fprintf(stderr, "预编译出错: %s\n", sqlite3_errmsg(db));
        return -1;
    }

    // 绑定参数（姓名）
    if(employee_name != NULL)
        sqlite3_bind_text(stmt, 1, employee_name, -1, SQLITE_TRANSIENT);

    int found = 0; // 用于记录找到的员工记录数量

    // 显示查询结果表头
    printf("\n=== 员工查询结果 ===\n");
    printf("员工ID\t姓名\t职位\t工号\n");
    printf("--------------------------------------------------\n");

    // 执行查询并遍历结果
    while(sqlite3_step(stmt) == SQLITE_ROW){
        // 读取当前行的各列数据
        int employee_id = sqlite3_column_int(stmt, 0);                    // 第0列：employee_id
        const unsigned char *name = sqlite3_column_text(stmt, 1);         // 第1列：employee_name
        const unsigned char *position = sqlite3_column_text(stmt, 2);     // 第2列：position
        const unsigned char *employee_num = sqlite3_column_text(stmt, 3); // 第3列：employee_num
//        const unsigned char *permission = sqlite3_column_text(stmt, 4);   // 第4列：permission

        // 格式化输出员工记录
        printf("%d\t%s\t%s\t%s\n",
               employee_id,
               name ? name : "NULL",
               position ? position : "NULL",
               employee_num ? employee_num : "NULL");
//               permission ? permission : "NULL");
        found++; // 正确计数找到的记录数量
    }

    // 输出查询结果统计
    if(!found){
        if(employee_name != NULL){
            printf("未找到姓名为 '%s' 的员工\n", employee_name);
        } else {
            printf("员工表中暂无数据\n");
        }
    } else {
        printf("--------------------------------------------------\n");
        printf("共找到 %d 条员工记录\n", found);
    }

    // 释放语句句柄
    sqlite3_finalize(stmt);
    return found; // 返回找到的记录数量
}

//插入员工考勤日记
int insert_attendance_user( ATTENDANCE * attendance){
    char sql[256];
    sprintf(sql," INSERT INTO attendance(attendance_id,employee_id,attendance_date,clock_in_time, "
                " clock_out_time ,attendance_status, leave_hours)VALUES(%d,%d,'%s','%s','%s','%s','%s');",
            attendance->attendance_id,attendance->employee_id,attendance->attendance_date,attendance->clock_in_time,
            attendance->clock_out_time,attendance->attendance_status,attendance->leave_hours);
    char *errmsg = NULL;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr,"attendance表插入数据失败:%s",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("attendance表插入数据成功");
    return 0;
}

int update_attendance_user(ATTENDANCE * attendance){
    char sql[256];
    sprintf(sql," UPDATE attendance "
                " SET "
                " employee_id = %d,"
                " attendance_date = '%s',"
                " clock_in_time = '%s',"
                " clock_out_time = '%s',"
                " attendance_status = '%s',"
                " leave_hours = '%s'"
                " WHERE attendance_id = %d;"
                ,attendance->employee_id,attendance->attendance_date,attendance->clock_in_time,
            attendance->clock_out_time,attendance->attendance_status,attendance->leave_hours,attendance->attendance_id);
    char *errmsg = NULL;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr,"attendance表更新数据失败:%s",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("attendance表更新数据成功");
    return 0;
}

int delete_attendance_user(ATTENDANCE * attendance){
    char sql[256];
    sprintf(sql,"delete from attendance  where attendance_id =%d;"
            ,attendance->attendance_id);
    char *errmsg = NULL;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr,"attendance表删除数据失败:%s",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("attendance表删除数据成功");
    return 0;
}
int query_attendance_name(char* employee_name){
    // 定义查询SQL，根据员工姓名联表查询考勤信息
    const char *sql = NULL;

    if (employee_name == NULL){
        // 查询所有员工的考勤记录
        sql = "SELECT a.attendance_id, a.employee_id, e.employee_name, a.attendance_date, "
              "a.clock_in_time, a.clock_out_time, a.attendance_status, a.leave_hours "
              "FROM attendance a JOIN employee e ON a.employee_id = e.employee_id "
              "ORDER BY a.attendance_date DESC, e.employee_name;";
    } else{
        // 根据员工姓名查询特定员工的考勤记录
        sql = "SELECT a.attendance_id, a.employee_id, e.employee_name, a.attendance_date, "
              "a.clock_in_time, a.clock_out_time, a.attendance_status, a.leave_hours "
              "FROM attendance a JOIN employee e ON a.employee_id = e.employee_id "
              "WHERE e.employee_name = ? "
              "ORDER BY a.attendance_date DESC;";
    }

    // 定义SQL语句执行句柄
    sqlite3_stmt *stmt;

    // 预编译SQL语句
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if(rc != SQLITE_OK){
        fprintf(stderr, "预编译出错: %s\n", sqlite3_errmsg(db));
        return -1;
    }

    // 绑定参数（员工姓名）
    if(employee_name != NULL)
        sqlite3_bind_text(stmt, 1, employee_name, -1, SQLITE_TRANSIENT);

    int found = 0; // 用于记录找到的考勤记录数量

    // 显示查询结果表头
    printf("\n=== 考勤查询结果 ===\n");
    printf("考勤ID\t员工ID\t员工姓名\t考勤日期\t上班时间\t下班时间\t状态\t请假时长\n");
    printf("--------------------------------------------------------------------------------\n");

    // 执行查询并遍历结果
    while(sqlite3_step(stmt) == SQLITE_ROW){
        // 读取当前行的各列数据
        int attendance_id = sqlite3_column_int(stmt, 0);          // 第0列：attendance_id
        int employee_id = sqlite3_column_int(stmt, 1);            // 第1列：employee_id
        const unsigned char *name = sqlite3_column_text(stmt, 2); // 第2列：employee_name
        const unsigned char *attendance_date = sqlite3_column_text(stmt, 3); // 第3列：attendance_date
        const unsigned char *clock_in_time = sqlite3_column_text(stmt, 4);    // 第4列：clock_in_time
        const unsigned char *clock_out_time = sqlite3_column_text(stmt, 5); // 第5列：clock_out_time
        const unsigned char *attendance_status = sqlite3_column_text(stmt, 6); // 第6列：attendance_status
        const unsigned char *leave_hours = sqlite3_column_text(stmt, 7);     // 第7列：leave_hours

        // 格式化输出考勤记录
        printf("%d\t%d\t%s\t%s\t%s\t%s\t%s\t%s\n",
               attendance_id,
               employee_id,
               name ? name : "NULL",
               attendance_date ? attendance_date : "NULL",
               clock_in_time ? clock_in_time : "NULL",
               clock_out_time ? clock_out_time : "NULL",
               attendance_status ? attendance_status : "NULL",
               leave_hours ? leave_hours : "NULL");

        found++;
    }

    // 输出查询结果统计
    if(!found){
        if(employee_name != NULL){
            printf("未找到员工 '%s' 的考勤记录\n", employee_name);
        } else {
            printf("考勤表中暂无数据\n");
        }
    } else {
        printf("--------------------------------------------------------------------------------\n");
        printf("共找到 %d 条考勤记录\n", found);
    }

    // 释放语句句柄
    sqlite3_finalize(stmt);
    return found; // 返回找到的记录数量
}
